Excel worksheet with macro scripting for AF

Author: Walter | Created: 2009-03-24 9:44 | Last Activity: 2009-07-22 13:31

Original Post

2009-03-24 9:44
Hi,

I created an Excel worksheet with some macro scripting to support AF.
This is what's implemented by now:

- It supports the creation of multiple notebooks in one file. Each worksheet is a notebook; each worksheet column is a list (page).

- It lets the user to define the amount of lines per list (page), will show exactly that amount in the worksheet (by hiding the rest), and won't let the user to input anything past it. It also allows redefining this amount whenever needed, and will rearrange the lists accordingly. For example: If a notebook was set to 30 lines per list, and later re-arranged to 15 lines per list, all the existing tasks will be redistributed to 4 pages.

- It supports shortcuts to "Complete", "Dismiss" and "Pause" a task. This last one crosses off the task, and re-adds it at the end of the last active page. It also allows re-opening a task that's been dismissed or completed (though this shouldn't be used).

- It monitors each list, and will show them dimmed when all of their tasks are either completed or dismissed.

- It lets the user to jump to the first active page, and to add a task at the end of the last active page.

Shortcuts:

CTRL-Shift-A: Re-arranges a list (Asking lines/page)
CTRL-Shift-N: Creates a new task at the end of the last active page
CTRL-Shift-C: Crosses off a task as completed
CTRL-Shift-D: Marks a task as dismissed (with yellow background)
CTRL-Shift-P: "Pauses" a task (crosses-it off, re-adds it at the end)
CTRL-Shift-O: Restores a task to active status
CTRL-Shift-F: Jump to the first active page

Frankly, I created this until I decide if I want to split my tasks into work/home. It apparently works ok (I only started using it yesterday), though the feeling is that AF works somehow better on paper, as many say. (I made it more or less look like paper, anyway :)

So, for whoever's interested, even out of curiosity, here's the link for downloading:
http://rapidshare.com/files/212885404/AF.xls.html

Walter

Reply by Christine B

2009-03-24 10:16
Interesting development Walter!

I feel, as you suggest above, that AF does indeed work best on paper but also think there is scope for a digital option to be run alongside to handle some of our own individual requirements, such as separating out specific tasks when required. I myself use MS OneNote for that but can see this would be a great tool for those who preferred the Excel option.

Reply by Walter

2009-03-24 14:41
Hi Christine,

I feel that by writing the lists on paper I somehow internalize the tasks better.
But I have to say that I've been using the file today and it flowed quite well, probably because all the "pages" are shown one next to the other.

Walter

Reply by Geoff

2009-03-24 15:46
Walter,

I would be interested to take a lool but get the following error. I'll check in a day or two's time to see if I can access it.

"This file is neither allocated to a Premium Account, or a Collector's Account, and can therefore only be downloaded 10 times.

This limit is reached.

To download this file, the uploader either needs to transfer this file into his/her Collector's Account, or upload the file again. The file can later be moved to a Collector's Account. The uploader just needs to click the delete link of the file to get further information.

Reply by Walter

2009-03-24 16:03
Hi Geoff,

I created a Collector's Account. Please try to download again using the same link.
If it doesn't work I'll upload the file to another site.

Reply by moises

2009-03-24 17:14
Walter,

Very nice!

Maybe add a macro to change the background color of the cell with the currently active task? This would correspond to the dot that Mark recommends putting next to the item that is being worked on now.

Reply by Walter

2009-03-24 22:57
Thanks Moises,

I wasn't aware of the dot recommendation. I'll think about how to implement that tomorrow.

Walter

Reply by Mike

2009-03-25 10:32
Walter,

You might copy the list of shortcuts into a page at the end of the workbook.

Reply by Walter

2009-03-25 16:30
Hi,

Moises, I added a shortcut for marking the task you're working on: CTRL-Shift-W

Mike, I added a help popup that's activated with CTRL-Shift-H.

I also added options to Excel's right click menu (try it on any task). They should only appear when working on the AF file.

I'm still testing this, you're warned :-)

New download URL: http://rapidshare.com/files/213403509/AF.xls

Mark (Mr. Forster), if you see this: I hope I'm not doing anything wrong by posting the file here. I will remove it immediately at your request.

Thanks,
Walter

Reply by Joshua

2009-04-16 16:10
Walter,

Just a quick "Thank You" for developing and sharing your Excel AF solution. I am not using it myself, but my fiancee loves it!

Kind regards,

Joshua

Reply by kyznetslov

2009-04-18 16:22
what are the varIious columns for in each excel worksheet? i'm just looking at this now as a new tool, so need some guidance. the af 'manual' isn't helping much yet. there is some date on worksheet 2 and 3, but not in any of the languages that i can read. maybe you could send a file with several tasks entered in to demonstrate the system

Reply by Joshua

2009-04-18 19:00
The "columns" are the "pages" in AF.
The worksheets are individual "notebooks", in this file "work" and "home".
The last worksheet is a "Backup" worksheet.

You simply enter the tasks per line and then use the macro shortcuts (press CTRL + SHIFT + H for a list) to work through the "notebook".

Hope that helps,

Joshua

Reply by Walter

2009-04-18 21:49
Wow, I'm glad to hear that.

I'm using it myself, though I developed it just for the sake of it.

I also installed DropBox at home and at my office, so my file is accessible from both points without copying it each time.

Thanks for the kind words :-)

Walter

Reply by Joshua

2009-04-18 23:34
Walter,

Thank YOU for developing it!

Here's a little glitch I noticed: The rearranging macro unhides the previously hidden rows below the AF "page". Not sure why that is so - does the same thing happen for you? I remember trying this in the file's first version you posted here with no such problem. Did you change anything in that macro that could be causing the problem?

This does not affect usability technically, but since I am a highly visual person, I would love to get the "hiding" back. Unfortunately I do not know enough about Excel yet to be able to do it manually. Maybe you could have a look at this in your file and update it? That would be great!

Thank you,

Joshua

Reply by Joshua

2009-04-19 0:28
Walter,

I think I know what happened - I changed your .xls to xlsm, the new 2007 macro format. That must have blown it apart.

Joshua

Reply by Joshua

2009-04-19 0:33
... and here's my (hopefully) last post on this: I'll simply go back to the .xls where everything works fine.

:-)

Thank you for bearing with me,

Joshua

Reply by Walter

2009-04-19 8:44
Hi Joshua,

If it's more convenient for you, I can adapt it to xlsm. I have Office 2007 at work, but I decided to use the old format for compatibility with older versions.

Please let me know. It shouldn't be much effort at all.

Walter

Reply by Joshua

2009-04-20 0:25
Walter,

Thank you very much for your kind offer! It would indeed be great if you could do that.

By the way, how do you mark the active page you're on (for finding your place after either inserting a new task or "pausing" a task OR after working in the other worksheet)?

Joshua

Reply by Walter

2009-04-20 13:45
Hi Joshua,

I uploaded the xlsm file, you can find it here:

http://rapidshare.com/files/223571690/AF.xlsm

About your other questions, right now there are two options:

- You can press Ctrl-Shift-W, which will mark the current task in green, as a visual reference. If you mark another task, the previous one will be restored to white. I should add a shortcut to jump to it's position, I'll take a look at it.

- You can move to the first active page with Ctrl-Shift-F

That's for now :-)

Walter

Reply by Walter

2009-04-20 14:55
Here's the new version:

Ctrl-Shift-W sets the working bookmark
Ctrl-Shift-R returns to the bookmark's position

Excel 2007 version: http://rapidshare.com/files/223594561/AF.xlsm
Excel 2003 version: http://rapidshare.com/files/223594352/AF.xls

Walter

Reply by Joshua

2009-04-20 17:10
Hi Walter,

Thank you very much, that's great! CTRL + Shift + R should come in very handy.

Unfortunately, the rearranging still does the same weird thing in 2007 as it did before. After rearranging, the rows below the "page" are not hidden anymore. Not sure why that happens.

Kind regards,

Joshua

Reply by Rod

2009-04-20 23:53
Walter,
I really like what you've done here! I've been using it for a few days and have only one suggestion for an improvement. When an item is marked completed and gets a strikethrough, how about if at the same time it was made subscript size or made smaller some other way? Then, because it would be smaller on the page, undone items would stand out a bit more on the page. I did that on another program I was using and found it very helpful.

Reply by Walter

2009-04-21 15:35
Hi, Joshua
This is really weird. I use it at work and at home it does hide the rest of the rows.
I wonder if I understand you correctly. You mean that:

- You press CTRL-Shift-A
- A popup appears, asking you to specify the amount of lines per page, lets say 30
- After pressing ok, you see some screen activity

.... and after that, you are still seeing the rows 31 up to 65536?

Are you able to hide rows manually?
If you mark a few lines (complete lines, by clicking on the row numbers), then right-click and select "Hide", do they get hidden?

After you answer this, I would send you a single VBScript line (the one that hides the rows) to see if it works.

Walter

Reply by Walter

2009-04-21 15:37
Hi Rod,

I'll check how it looks reducing the font to 6 points. I'll let you know.

:-)
Walter

Reply by Joshau

2009-04-26 0:17
Walter,

Thank you for all the effort you put into this!

I will try to respond to your questions as clearly as possible.

---

Walter:

"This is really weird. I use it at work and at home it does hide the rest of the rows.
I wonder if I understand you correctly. You mean that:

- You press CTRL-Shift-A
- A popup appears, asking you to specify the amount of lines per page, lets say 30
- After pressing ok, you see some screen activity

.... and after that, you are still seeing the rows 31 up to 65536?"

Joshua:

No. I see the rows from 65537 onwards. :-) Could this have to do with the fact that in Office 2007 they increased the maximum number of rows in Excel?

Walter:

"Are you able to hide rows manually?"

Joshua:

Yes.

Walter:

"If you mark a few lines (complete lines, by clicking on the row numbers), then right-click and select "Hide", do they get hidden?"

Joshua:

Yes.

Reply by Walter

2009-04-26 10:34
Hi Joshua,

I wasn't aware of Excel 2007's new row limit, as I only use "compatibility mode". And it was that, indeed.

New versions:

Excel 2007: http://rapidshare.com/files/225872523/AF.xlsm
Excel -2003: http://rapidshare.com/files/225872522/AF.xls

I hope it's ok now :-)

Walter

Reply by Joshua

2009-04-27 13:40
Walter,

Yes, it works! Thank you so much! :-)

Joshua

Reply by Derek D

2009-05-19 13:07
I've decided to give this a go as it seems very simple and effective, thanks Walter.

Reply by Derek D

2009-05-22 13:35
Just a note to say this is working very well for me, many thanks again Walter.

Regards,

Derek.

Reply by Donna Whitehouse

2009-05-22 15:11
Hi Walter,

I've just downloaded your s/s and transferred my existing list into the work sheet, comprising 80 odd items. However, I've just glanced at the back-up sheet, and noticed it has only got a back up of the first 30 items (ie column a). I'm using the 2007 version.

Is this me, or is this how it is supposed to work?

Thanks

donna

Reply by Walter

2009-05-24 10:10
Hi Donna,
The Backup sheet gets updated only when using CTRL-Shift-A (rearrange).

Lets say the list is set to 30 lines per "page" (column), and the you rearrange it to 40, then the backup will show 30 lines.
Notice that there's no formatting, so the columns will be shown with standard width, etc.

Try using rearrange and if you find any problem let me know.

I'm glad some of you are using the implementation :-)

Walter

Reply by Donna Whitehouse

2009-05-27 16:07
Hi Walter,
Thanks for that update, I hadn't done a rearrange, so hadn't appreciated that it was linked to that.

I do have one other problem that I'm hoping you can help with. If I Pause a task, it takes me to the newest item on the list (ie the item that's just been re-added at the end). I then have to go through my whole list (currently 4 pages) to find the item I've just crossed off to consider the next item (and I may have to do this a number of times, if I've re-added it several times). I thought that the Ctrl-Shft-R command would do this, but it doesn't.

If you can help, that would be great.

Reply by Derek D

2009-06-29 22:08
Walter, will you be updating the excel sheet for AF2?

Many thanks,

Derek

Reply by Walter

2009-07-1 9:12
Hi Derek,

Yes, I'm working on it. I want it to support both AF and AF2 transparently (though I'm converting to AF2 quicker than I thought).

Will post a link asap :-)

Reply by Derek D

2009-07-2 23:31
Great! Thanks Walter.

Reply by Mike

2009-07-3 12:03
Walter,

I'm in the midst of converting my ListPro tasks to Excel. I have not done all of the whizbang that you have to make a nice user interface. My purpose was to just implement metrics so I could keep a history of how the system was working. If it interests you, it might be useful to add that kind of thing. Here is a preliminary list of things I'd want to track:

- Number of currently open tasks
- Number of closed tasks (total and for this month or since some parametrized date)
- Number of dismissed tasks (as above)
- For each task (days active, age when closed, age when dismissed)
- Average and maximum ages for the above (for the total and this month or since some date)

I'm sure you could think of other interesting metrics. My thought was to have a second "sheet" which was basically a table by day so I could see how things were working. I might even go hog wild and do some graphing ;-)

Basically I want to be able to ask the question: "If I changed the dismissal rule this way, what would be the throughput, velocity, maximum age of items, etc.? How would it change?"" As it is now, I FEEL as if task are aging less before they are handled, in AF2, but I don't have any objective data for why I feel that way.

Let me know your thoughts. I'll keep hammering on what I'm doing and maybe I can just add to what you come up with. In which case, I'll shave.

Reply by Mike

2009-07-3 12:21
Walter,

I guess I'm a bit dim this morning. I should have gone to look at your implementation again. The way you have it implemented (with only one column for each page) I can see that what I am trying to do will not fit. Oh well, back to my Excel sheet where I have multiple columns per task and go on down with the rows forever ;-) ;-)

Reply by JD

2009-07-3 14:43
Walter - I just had a look at your set up, and would like to add my thanks for sharing your work. This will be very useful in my work with other members in the team.

Reply by Jens

2009-07-6 9:17
Walter,
thanks for sharing. I'm trying your xls at work (private stuff I prefer paper).
If you think about more features I would suggest to add something like a due-date management (e.g. to give a task a due date and if it's due or overdue the fontcolor changes).

Reply by Greenchutney

2009-07-7 1:37
Walter,
I decided to move from OneNote to Excel. Your spreadsheet has been fantastic. I just made a small tweak to change the font color when the item is complete so the incomplete items stand out better. It has been working great!

Eagerly awaiting your updates for AF2. Thanks for the wonderful spreadsheet.

GC

Reply by Jens

2009-07-7 10:07
Greenchutney,
<< I just made a small tweak to change the font color when the item is complete so the incomplete items stand out better. >>

I had tried this also, but it doesn't work. I had added in the module 'global stuff' the
Public Sub SetTaskStatus
...
Case TaskStatuses.TaskStatusComplete
...
ActiveSheet.Cells(lngRow, intCol).Font.Color = RGB(128, 128, 128)

I'm far from being an expert in VB-Scripting...
Can you give me a hint?
Bye, Jens

Reply by Jens

2009-07-7 12:45
Walter,
how can I avoid that SHIFT-STRG-A (Rearrange current List) will delete comments?
Thanks, Jens

Reply by Jens

2009-07-7 12:47
... and will be also copied when "stop working on task"

Reply by Greenchutney

2009-07-7 18:16
Jens,
I am not a vbcode expert either :)

You are correct -- setting the font in that code doesn't work. The font color appears to be reset later on. I used the following instead to mark the items more clearly. Don't use Green because it's being used to mark a working task.

ActiveSheet.Cells(lngRow, intCol).Interior.Color = vbCyan

Walter please correct me if I am wrong.

Reply by Walter

2009-07-8 15:32
Wow!

I've been really busy and couldn't check the forum for a couple of days. I apologize for not replying before.

I couldn't update the worksheet to AF2 until now, and I've been using it "manually" myself (drawing the separator line). I've just added a shortcut to do this: Ctrl-Shift-L draws a red line, another hit will remove it.

I've been thinking about automating the whole thing like this:

- Save somewhere (in a hidden sheet?) when was the last time I opened the file.
- When I open it again, check If more than a day has passed since the last time. If so, look for the last "on notice" line, and dismiss automatically the tasks before it that weren't completed.
- Draw the next line automatically.

But I have the feeling that it'd be taking it too far, because that way I wouldn't "internalize" the situation, if you know what I mean, and it would affect the system negatively. The program would know my tasks better that me. I also want to keep the "manual" spirit of AF/2 to some extent.

So I decided not to do it ;-)

I think that Ctrl-Shift-L shortcut is enough. If you have further ideas I'll be glad to hear them.

I'll answer now your posts.

Reply by Walter

2009-07-8 15:38
Mike:

"I'm in the midst of converting my ListPro tasks to Excel. I have not done all of the whizbang that you have to make a nice user interface. My purpose was to just implement metrics so I could keep a history of how the system was working. If it interests you, it might be useful to add that kind of thing. Here is a preliminary list of things I'd want to track:

- Number of currently open tasks
- Number of closed tasks (total and for this month or since some parametrized date)
- Number of dismissed tasks (as above)
- For each task (days active, age when closed, age when dismissed)
- Average and maximum ages for the above (for the total and this month or since some date)"


I could provide a certain amount of information, like number of open tasks, number of dismissed tasks... For the other metrics you mention, I would have to keep a lot more information about each task.

It is possible (as you mention, with an auxiliary sheet), but from my experience, it'd be overkill for Excel.

Maybe I should consider building a small app... though I find that the current Excel thing works (for me at least) because its simple.


Reply by Walter

2009-07-8 15:50
Jens,

About adding "due date": Please read my answer above to Mike. I would have to store it somewhere else (to hide it from the list), and it'd complicate things.

Additionally, I would like to keep it as close to the original system as possible. The (AF1) instructions state that one should keep these tasks in a calendar, and it works for me, at least.

Maybe if I create an application I would add it as an option for those who'd like to use it.

Reply by Walter

2009-07-8 16:16
Jens and Greenchutney:

About changing the font color. Both of your VBA examples appear to be correct.

It's not working since always after changing a task's status the procedure CheckColumnStatus is invoked.

It's purpose is to "dim" entire columns when all its tasks are either complete or dismissed. The font color is used for this (All the tasks' turn to light gray). When a column is still "active" all the tasks are set to black.


Jens, about copying comments. I'll check how can I do that. I use them quite a bit as well.

Walter


PS: Thanks for the kind words!

Reply by Jens

2009-07-8 16:17
Walter, a small portable app without bells an whistles would be great!!!

Reply by Mike

2009-07-8 20:33
Walter,

Thanks for the reply. I agree, your simple approach is just what the doctor ordered. ;-)

My approach (and one which allows for the kind of metrics which interest me) is to have a single sheet for all tasks (more like AF2 than AF1, now that I think about it). Doing it that way, and including Date Entered, Date Completed, and Date Dismissed, I can do just about anything I want. I have not spent time on it since I wrote you (too much to actually DO ;-) but I plan to do the metrics on a second sheet because I want the report to be a big table and I want to control the column widths. It is relatively simple to build a table of the dates in July (or any other moths(s)) then for each row, compare the date with dates on the first sheet and do counts, sums, averages, etc.

If I want to get info on pages open and closed, etc. I'm going to have to get just a tad more fancy, but I think it is doable. What I'm looking for is a snapshot of how my list is "going". Have I started to dismiss more or less? Have I been completing more or fewer tasks? Am I adding more tasks or fewer? What was the greatest number of open tasks on any date? The fewest?

You can kind of see where I'm going with this. As of now, I can "feel" that the AF2 list is moving faster, but I'm not using ANY dismissal rules ... except that if I decide that something is not going to get done (because it is moot, for example) I dismiss it. My reason is that I have no way right now of measuring alternative dismissal strategies and I need to be able to do that before I start dismissing things. For me, the overhead of dismissing and scanning lists of someday/maybe to add them back is not something I want to incur right now. Once I get my sheet up and running, I'm going to have "N" days in July where I used no dismissal rules. Then I can compare the AF2 rule with the AF1 rule and perhaps combine both of the rules (which really ought to speed things up.) Anyhow, lacing a means to tell for sure, I'm on hold for now, just recording and annotating dates in my pocket AF list against the day when I'll have the tool to measure my progress.

I'll let you know when I get it done and you'll be welcome to have it ... if it suits you.

Thank you again for taking the initiative to produce a very nice and useful too.

Reply by Walter

2009-07-19 13:14
Hi,

I apologize for the delay, I'm having some crazy days :-/

I just posted the new version. It only includes the new command:

CTRL-Shift-L: Insert AF2 dismissal line

....but also addresses the problem with the comments, reported by Jens. Now they are copied with a task after "pausing" it, and also when rearranging the list.

I'm tempted to remove the right-click menu (for some reason I feel it doesn't fit). If somebody uses it I'll keep it, though.

Download link: http://rapidshare.com/files/257541636/AF.xls

Walter

Reply by Jens

2009-07-20 20:36
Walter,
thanks a lot for the update.
If the right-click menue doesn't make extra efforts, please keep it...

Reply by Jens

2009-07-21 11:33
Walter,
is it ok to simply copy tasks from the old list to your new AF.xls?

Reply by Walter

2009-07-22 13:31
Hi, Jens

Yes, it should be. Just configure the "pages" to have to same amount of rows.